#!/usr/bin/env python
# -*- encoding: utf-8 -*-
'''
@File    :   config_utils.py    
@Contact :   pengwei.sun@aihuishou.com
@License :   (C)Copyright aihuishou

@Modify Time      @Author       @Version    @Desciption
------------      -----------   --------    -----------
2021-08-25 15:56   pengwei.sun      1.0         None
'''
from src.utils.db_processor import presto_processor,mysql_prediction_processor
import numpy as np
import sys

#等级预先排序
# BASE_LEVEL_RANK="""
# SELECT
# product_level_id,product_level_name,product_level_order_rank, product_level_order_rank as product_level_order_rank_52
# FROM dim.dim_product_level a
# WHERE product_category_id = 6 AND is_product_level_active_flag = 1
# """

BASE_LEVEL_RANK="""
select case when a.product_level_id   is null then cast(b.product_level_id as int) else cast(a.product_level_id as int) end product_level_id,
case when level.product_level_name is null then levelb.product_level_name else level.product_level_name end product_level_name,
case when b.rank is null then cast(a.rank as int) else cast(b.rank as int) end as product_level_order_rank,
case when a.rank is null then cast(b.rank as int) else cast(a.rank as int) end  as product_level_order_rank_52
from 
(
select * 
from c2b.doba_biprice_levelRank 
where product_brand_id in ('6052')) a
full join (
select * 
from c2b.doba_biprice_levelRank 
where product_brand_id in ('601')) b
on a.product_level_id=b.product_level_id
left join dim.dim_product_level level
on cast(a.product_level_id as int) =level.product_level_id
left join dim.dim_product_level levelb
on cast(b.product_level_id as int) =levelb.product_level_id

"""

LEVEL_RANK="""
SELECT 
a.level_key as product_level_id,level.level_name as product_level_name ,a.template_id AS product_level_order_rank,a.template_id_52 AS product_level_order_rank_52
FROM sku2_table_level_apple_rank a 
inner join warehouse.dim_product_level level
on a.level_key =level.level_id
"""

#型号的全量等级
product_level_sql="""
select distinct a.*,level_template.*,level_template.product_level_id as level_id,
concat(concat(cast(level_template.secondary_level_template_id as varchar) ,'_') ,cast(a.product_brand_id as varchar)) as template_brand,
 level_template.secondary_level_template_id as level_template_id,
level.product_level_name from
(
SELECT distinct
    dp.product_brand_id,
    dp.product_name,
    dp.product_id
FROM dim.dim_product dp JOIN dim.dim_product_sku_channel_mapping dps ON dp.product_id = dps.product_id  and dps.business_channel_id=1
WHERE dp.product_category_id=6 and dp.product_id_status_id=2 
) a
left join
ods.ods_opt_foundation_secondary_product_template_mapping otpm 
on a.product_id=otpm.product_id
left join ods.ods_opt_foundation_secondary_product_level_template_level level_template
on otpm.secondary_level_template_id=level_template.secondary_level_template_id 
left join dim.dim_product_level level
on level_template.product_level_id=level.product_level_id
where  otpm.business_channel = 1
"""

apple_product_ids = [7424, 7425, 8218, 8757, 9021, 9640, 9641, 15144, 15145, 17853, 17882, 24505, 25399, 25471, 26470, 27969, 28924, 28925, 29185, 29186, 32411, 34571, 34572, 35818, 35819, 38508, 38541, 43514, 43598, 66429]

def get_level_rank():
    # level_rank_df = presto_processor.load_sql(BASE_LEVEL_RANK)
    # level_rank_df[['product_level_id']]=level_rank_df[['product_level_id']].apply(np.int64)

    level_rank = mysql_prediction_processor.load_sql(LEVEL_RANK)
    level_rank[['product_level_id']]=level_rank[['product_level_id']].apply(np.int64)

    # level_rank_df=level_rank_df[['product_level_id','product_level_name']].merge(level_rank,on='product_level_id')
    return level_rank

level_rank_df=get_level_rank()


# self.level_rank_df =postgre_processor.load_sql(BASE_LEVEL_RANK)
product_level_df =presto_processor.load_sql(product_level_sql)

"""
处理等级比率：价格段贴近时，给出不同的数量和价格段，对价格的上下波动限制
"""
# 贴近最近出货价的比率计算逻辑：c端给出不通价格段的贴近比率上下限
def price_range_rate(price_0_7, sale_num_0_7):
    if price_0_7 > 0 and price_0_7 < 1000:
        if sale_num_0_7 >= 10:
            rate = 0
        elif sale_num_0_7 >= 5:
            rate = 0.04
        elif sale_num_0_7 >= 1:
            rate = 0.08
    elif price_0_7 >= 1000 and price_0_7 < 3000:
        if sale_num_0_7 >= 10:
            rate = 0
        elif sale_num_0_7 >= 5:
            rate = 0.018
        elif sale_num_0_7 >= 1:
            rate = 0.036
    elif price_0_7 >= 3000 and price_0_7 < 6000:
        if sale_num_0_7 >= 10:
            rate = 0
        elif sale_num_0_7 >= 5:
            rate = 0.01
        elif sale_num_0_7 >= 1:
            rate = 0.02
    elif price_0_7 >= 6000:
        if sale_num_0_7 >= 10:
            rate = 0
        elif sale_num_0_7 >= 5:
            rate = 0.008
        elif sale_num_0_7 >= 1:
            rate = 0.016
    else:
        rate = 0
    return rate

"""
贴近最近出货价：
根据业务给出的，不同价格段和不通出货量，允许价格的上下限波动比率严格执行
"""
def sale_num_price_fun(level_sub,sale_num_0_7, price_0_7, thisprice, forecast_reference_price):
    if np.isnan(sale_num_0_7) and np.isnan(price_0_7):
        return forecast_reference_price
    rate = price_range_rate(price_0_7, sale_num_0_7)
    if sale_num_0_7 == 0:
        if thisprice > 0:  # 如果最近出货价为0，则贴近下个周期的价格
            if level_sub in ['K']:
                forecast_reference_price = thisprice*0.8
            else:
                forecast_reference_price = thisprice
        return forecast_reference_price

    if sale_num_0_7 > 0:
        if level_sub in ['K']:
            forecast_reference_price = price_0_7 * 0.85
        else:
            forecast_reference_price = price_0_7
    # else:
    #     if forecast_reference_price > price_0_7 * (1 + rate):
    #         forecast_reference_price = price_0_7 * (1 + rate)
    #     elif forecast_reference_price < price_0_7 * (1 - rate):
    #         forecast_reference_price = price_0_7 * (1 - rate)

    return forecast_reference_price


"""
S等级：取数价格4个周期段内的最大最小价格：
S等级的价格 受到4个周期端内的价格上下限限制，不能过高 也不能过低
"""
def s_min_max_range_fun(level, min_week_price, max_week_price, forecast_reference_price):
    if np.isnan(min_week_price) and np.isnan(max_week_price):
        return forecast_reference_price

    if min_week_price > 0 and max_week_price < sys.maxsize and level == 'S':
        if forecast_reference_price < min_week_price:
            forecast_reference_price = min_week_price
        elif forecast_reference_price > max_week_price:
            forecast_reference_price = max_week_price
    return forecast_reference_price

if __name__ == '__main__':
    # level_rank_df=get_level_rank()
    print(1)